import os
import sys
import time
import subprocess
import threading
import signal
import socket

from copy import copy
from openpyxl import load_workbook
from utils import *
from excel_file import Cell




class Table:
    def __init__(self, table_range, sheet_name, sheet_id, spreadsheet_name, spreadsheet_id, wb, col_header, row_header):
        # sheetname 不会重名
        # 列头宽度必须与
        self.wb = copy(wb)
        self.sheet = self.wb[sheet_name]
        # 列头范围字符串
        self.col_header_range_str = col_header
        # 行头范围字符串
        self.row_header_range_str = row_header
        self.has_col_header = True
        self.has_row_header = True
        # 判断列头字符串是否有效
        if len(self.col_header_range_str) == 0 or self.col_header_range_str is None:
            # 如果无效则设置has_col_header为False
            self.has_col_header = False
        # 判断航头字符串是否有效
        if len(self.row_header_range_str) == 0 or self.row_header_range_str is None:
            # 如果无效则设置has_row_header为False
            self.has_row_header = False
        # 表格的范围字符串
        self.table_range = table_range
        # 该表格属于的sheet
        self.belong_to_sheet_name = sheet_name
        # 该表格属于的Sheet id
        self.belong_to_sheet_id = sheet_id
        # 该表格属于的excel文件名
        self.belong_to_spreadsheet_name = spreadsheet_name
        # 该表格属于的excel文件id（或能够唯一确定表格的字符串）
        self.belong_to_spreadsheet_id = spreadsheet_id
        # 为该表格生成唯一编码
        self.id = generate_uuid()
        # 解析表格的范围
        self.left_pos_col, self.left_pos_row, self.right_pos_col, self.right_pos_row = parser_range_str(table_range)
        # 将标签转换成坐标
        self.left_top_col_coor = trans_pos_label_to_coor(self.left_pos_col)
        self.left_top_row_coor = trans_pos_label_to_coor(self.left_pos_row)
        self.right_bot_col_coor = trans_pos_label_to_coor(self.right_pos_col)
        self.right_bot_row_coor = trans_pos_label_to_coor(self.right_pos_row)
        # 定义表格的左上和右下坐标
        self.min_col = self.left_top_col_coor
        self.min_row = self.left_top_row_coor
        self.max_col = self.right_bot_col_coor
        self.max_row = self.right_bot_row_coor

        self.left_of_col_header_pos_col = None
        self.left_of_col_header_pos_row = None
        self.right_of_col_header_pos_col = None
        self.right_of_col_header_pos_row = None
        self.left_of_col_header_col_coor = None
        self.left_of_col_header_row_coor = None
        self.right_of_col_header_col_coor = None
        self.right_of_col_header_row_coor = None
        self.col_header_ranges = None
        self.col_header = None

        self.left_of_row_header_pos_col = None
        self.left_of_row_header_pos_row = None
        self.right_of_row_header_pos_col = None
        self.right_of_row_header_pos_row = None 
        self.left_of_row_header_col_coor = None
        self.left_of_row_header_row_coor = None
        self.right_of_row_header_col_coor = None
        self.right_of_row_header_row_coor = None
        self.row_header_ranges = None
        self.row_header = None

        # 如果存在列头，则解析列头范围的信息
        if self.has_col_header:
            self.left_of_col_header_pos_col, self.left_of_col_header_pos_row, self.right_of_col_header_pos_col, self.right_of_col_header_pos_row = parser_range_str(self.col_header_range_str)
            self.left_of_col_header_col_coor = trans_pos_label_to_coor(self.left_of_col_header_pos_col)
            self.left_of_col_header_row_coor = trans_pos_label_to_coor(self.left_of_col_header_pos_row)
            self.right_of_col_header_col_coor = trans_pos_label_to_coor(self.right_of_col_header_pos_col)
            self.right_of_col_header_row_coor = trans_pos_label_to_coor(self.right_of_col_header_pos_row)
            self.col_header_ranges = (self.left_of_col_header_col_coor, self.left_of_col_header_row_coor, self.right_of_col_header_col_coor, self.right_of_col_header_row_coor)
            self.col_header = self.get_ranges_of_sheet(self.col_header_ranges)
        # 如果存在行头，则解析行头范围的信息
        if self.has_row_header:
            self.left_of_row_header_pos_col, self.left_of_row_header_pos_row, self.right_of_row_header_pos_col, self.right_of_row_header_pos_row = parser_range_str(self.row_header_range_str)
            self.left_of_row_header_col_coor = trans_pos_label_to_coor(self.left_of_row_header_pos_col)
            self.left_of_row_header_row_coor = trans_pos_label_to_coor(self.left_of_row_header_pos_row)
            self.right_of_row_header_col_coor = trans_pos_label_to_coor(self.right_of_row_header_pos_col)
            self.right_of_row_header_row_coor = trans_pos_label_to_coor(self.right_of_row_header_pos_row)
            self.row_header_ranges = (self.left_of_row_header_col_coor, self.left_of_row_header_row_coor, self.right_of_row_header_col_coor, self.right_of_row_header_row_coor)
            self.row_header = self.get_ranges_of_sheet(self.row_header_ranges)
        # 解析表格的Cell信息
        self.cells = self.gen_cells()
        

    def gen_cells(self):
        cells = []
        index_col = self.min_col
        index_row = self.min_row
        for index_row in range(self.min_row, self.max_row + 1):
            for index_col in range(self.min_col, self.max_col + 1):
                print(index_col, index_row)
                cell = self.sheet.cell(row=index_row, column=index_col)
                
                cell_object = Cell(cell)
                
                cell_object.classify_cell(col_header_range=self.col_header_range_str, row_header_range=self.row_header_range_str)
                # if self.has_col_header:
                #     if check_cell_in_range(cell_object.coordinate, self.col_header_range_str):
                #         cell_object.in_col_header = True
                #         cell_object.relationship = []
                #         cell_object.cell_type = 1
                        
                # if self.has_row_header:
                #     if check_cell_in_range(cell_object.coordinate, self.col_header_range_str):
                #         cell_object.in_row_header = True
                #         cell_object.relationship = []
                #         cell_object.cell_type = 1
                # if not cell_object.in_col_header and not cell_object.in_row_header:
                #     cell_object.gen_relationship(col_header=self.col_header, row_header, col_header_coors, row_header_coors)
                
                
                if cell_object.in_data_panel:
                    cell_object.gen_relationship(col_header=self.col_header, row_header=self.row_header, col_header_coors=self.col_header_ranges, row_header_coors=self.row_header_ranges)
                    cell_object.cell_type = 0
                else:
                    cell_object.relationship = []
                    cell_object.cell_type = 1
                cells.append(cell_object.format())
        return cells

    def get_ranges_of_sheet(self, ranges_coor):
        # 相对坐标
        ranges = []
        for i in range(ranges_coor[1], ranges_coor[3] + 1):
            row_ranges = []
            for j in range(ranges_coor[0], ranges_coor[2] + 1):
                temp_cell = copy(self.sheet.cell(row=i, column=j))
                row_ranges.append(temp_cell)
            ranges.append(row_ranges)
        return ranges
            
    def format_table(self):
        return_dict = {
            "excel_id": self.belong_to_spreadsheet_id,
            "excel_name": self.belong_to_spreadsheet_name,
            "sheet_id": self.belong_to_sheet_id,
            "sheet_name":self.belong_to_sheet_name,
            "cells": self.cells
        }
        return return_dict

def get_tables(sheetname):
    if sheetname == "Sheet1":
        table_range = 'A1:I9'
        return [table_range]
    elif sheetname == "Sheet2":
        table_range = "A1:F12"
        return [table_range]
def parse_table(table_range, sheetname, workbook):
    if sheetname=="Sheet1":
        col_head_range = 'A1:I1'
        row_head_range = 'A2:B9'
        return col_head_range, row_head_range
    elif sheetname=="Sheet2":
        col_head_range = "A1:F2"
        row_head_range = ""
        return col_head_range, row_head_range



wb = load_workbook("test2.xlsx", data_only=True)
sheetnames = wb.sheetnames


sheets = []
for index, sheetname in zip(range(len(sheetnames)), sheetnames):
    if sheetname=="Sheet1":
        table_ranges = get_tables(sheetname)
        for table_id, table_range in zip(range(len(table_ranges)), table_ranges):
            col_head_range, row_head_range = parse_table(table_range, sheetname, wb)
            print(sheetname)
            table = Table(table_range, sheetname, index, "test1.xlsx", "test1.xlsx", wb, col_head_range, row_head_range)
            print(table)
            print(table.format_table())
            dict_2_json(table.format_table(), f"{sheetname}.json")

